CREATE TABLE device_list (
    device_sn     NUMBER(9,0)         NOT NULL 
   ,device_id     VARCHAR2(15 BYTE)   NOT NULL 
   ,pond_id       NUMBER(9,0)         DEFAULT 0
   ,device_type   VARCHAR2(10 byte)    
   ,SIM           VARCHAR2(11 BYTE)   
   ,added_dt      DATE               DEFAULT SYSDATE 
   ,ip_addr       VARCHAR2(15 BYTE)   
   ,port_no       NUMBER(5,0)      
   ,status        VARCHAR2(1 BYTE)  DEFAULT 'N'   -- 状态，N-Normal正常，E-Error异常 W-Warning警告 P-Paused暂停使用
);
ALTER TABLE device_list ADD CONSTRAINT pk_device_list PRIMARY KEY ( device_sn);
CREATE UNIQUE INDEX udx_device_list_id ON device_list( device_id);
CREATE SEQUENCE device_list_sn_seq START WITH 15;

CREATE TABLE device_ip_hist(
    device_id       VARCHAR2(15 BYTE)     NOT NULL 
   ,device_sn     NUMBER(9,0)         NOT NULL 
   ,eff_dt          DATE                  NOT NULL 
   ,exp_dt          DATE
   ,ip_addr         VARCHAR2( 15 BYTE)    NOT NULL 
   ,port_no         NUMBER(5,0)           NOT NULL  
);
ALTER TABLE device_ip_hist ADD CONSTRAINT pk_device_ip_hist PRIMARY KEY ( device_id, eff_dt);

CREATE TABLE device_info_hist(
    device_id       VARCHAR2(15 BYTE)    NOT NULL 
   ,device_sn     NUMBER(9,0)         NOT NULL 
   ,eff_dt          DATE                  NOT NULL 
   ,exp_dt          DATE
   ,SIM             VARCHAR2( 11 BYTE) 
   ,poond_id        NUMBER(9,0)        
   ,device_type   VARCHAR2(10 byte)    
);
ALTER TABLE device_info_hist ADD CONSTRAINT pk_device_pond_hist PRIMARY KEY ( device_id, eff_dt);


CREATE TABLE sys_dd(
    sys_dd_id         NUMBER (5,0)            NOT NULL 
   ,dd_category_cd    VARCHAR2(15 BYTE)       NOT NULL   
   ,dd_value          VARCHAR2(10 BYTE)       NOT NULL    
   ,dd_name           VARCHAR2(52 CHAR)       
);

CREATE TABLE dev_incoming_log(               
     dev_incoming_logid    NUMBER(15,0)            NOT NULL 
    ,device_sn     NUMBER(9,0)         NOT NULL 
	,device_id             VARCHAR2(15 BYTE)       NOT NULL 
	,pond_id               NUMBER (9,0)
	,incoming_dt           DATE    DEFAULT SYSDATE NOT NULL  
	,data_type             VARCHAR2(1 BYTE)
	,incoming_data         VARCHAR2(42 BYTE)
);

CREATE  --- UNIQUE   ??? what if same device sent two msg within one second?
INDEX udx_dev_incoming_log ON dev_incoming_log ( device_id, incoming_dt);

CREATE TABLE dev_incoming_log_dtl(
     dev_incoming_logid     NUMBER(15,0)      NOT NULL 
	,dev_incoming_log_dtlid NUMBER(16,0)      NOT NULL 
	,sys_dd_id              NUMBER(5,0)       NOT NULL 
	,incoming_data_segment  VARCHAR2(10 BYTE)
);

CREATE TABLE dev_outgoing_log(
	 dev_outgoing_logid     NUMBER(15,0)       NOT NULL 
    ,device_sn              NUMBER(9,0)        NOT NULL 
	,device_id              VARCHAR2(15 BYTE)  NOT NULL 
	,created_dt             DATE    DEFAULT SYSDATE NOT NULL  
	,created_by             VARCHAR2(30 Byte)
	,outgoing_dt            DATE    
	,data_type              VARCHAR2(1 BYTE)
	,outgoing_data          VARCHAR2(128 BYTE)
	,status                 VARCHAR2(1 BYTE) DEFAULT 'N'
);
ALTER TABLE dev_outgoing_log ADD CONSTRAINT pk_dev_outgoing_log PRIMARY KEY ( dev_outgoing_logid);
CREATE INDEX idx_dev_outgoing_log ON dev_outgoing_log( device_id, outgoing_dt, status );


CREATE TABLE dev_outgoing_log_dtl(
	 dev_outgoing_logid     number(15,0)    NOT NULL 
	,dev_outgoing_log_dtlid number(16,0)    NOT NULL 
	,sys_dd_id              number(5,0)     NOT NULL 
	,outgoing_data_segment  varchar2(128 BYTE)
);

CREATE SEQUENCE dev_incoming_log_seq     START WITH 1;
CREATE SEQUENCE dev_incoming_log_dtl_seq START WITH 1;
CREATE SEQUENCE dev_outgoing_log_seq     START WITH 1;
CREATE SEQUENCE dev_outgoing_log_dtl_seq START WITH 1;

CREATE TABLE user_info (
	 user_id            NUMBER (9,0)      NOT NULL          
	,account            VARCHAR2(11 BYTE) NOT NULL 
	,username           VARCHAR2(30 CHAR)
	,passwordhash       VARCHAR2(1024 BYTE) 
	,alert_number       VARCHAR2(11 BYTE)
--	,alert_number_2     VARCHAR2(11 BYTE)
	,status             VARCHAR2(1 BYTE) default 'N'
	,created_dt         DATE  DEFAULT SYSDATE 
	,pass_eff_dt        DATE  DEFAULT SYSDATE 
	,pass_exp_dt        DATE  DEFAULT SYSDATE+7
	,country_id         NUMBER(7,0) default 1814991 --- CHN 
	,addr_id            NUMBER(12,0)  --- refer to table addr_code(addr_id) 
	,street_info        VARCHAR2(120 CHAR)   -- previous column address_detail 
	,city_name          VARCHAR2(50 CHAR) 
	,session_ct         NUMBER(2,0)   --- to control max session can one user login 
	,
);

ALTER TABLE user_info ADD CONSTRAINT pk_user_info PRIMARY KEY ( user_id);
CREATE UNIQUE INDEX udx_account ON user_info ( account);
CREATE UNIQUE INDEX udx_username ON user_info (username);

CREATE SEQUENCE user_id_seq    START WITH 1;

CREATE TABLE user_log_hist (
	 user_log_hist_id   NUMBER (11,0)           NOT NULL 
	,user_id            NUMBER (9,0)            NOT NULL          
	,login_dt           DATE   DEFAULT SYSDATE  NOT NULL 
	,login_status       VARCHAR2(1 BYTE)      ---S: succeed  F:fail    E:error 
	,session_id         VARCHAR2(512 BYTE)
	,remote_addr        VARCHAR2(20 BYTE)
);

CREATE SEQUENCE user_log_hist_id_seq    START WITH 1;
CREATE UNIQUE INDEX udx_user_log_hist ON user_log_hist( user_id, login_dt);

CREATE TABLE pond_info(
	 pond_id            NUMBER(9,0)          NOT NULL 
	,pond_name           VARCHAR2(30 CHAR)    NOT NULL 
	,user_id            NUMBER (9,0)  
	,area               NUMBER(7,1)  -- 单位：亩
	,height             NUMBER(5,1)  -- 单位： 米
	,prod               VARCHAR2(12 CHAR)
	,density            NUMBER(7,2) 
	,built_dt           DATE    --- 计算池塘年龄用  
	,source             VARCHAR2(12 CHAR )
	,oxy_method         VARCHAR2(12 CHAR )
	,floor_inf          VARCHAR2(12 CHAR )
	,ceil_info          VARCHAR2(12 CHAR )
	,earth_thickness    NUMBER ( 5,2)  -- 单位： 米
	,discharge_mod      VARCHAR2(12 CHAR )
	,oxy_power          NUMBER ( 7,2) 
	,alarm_call1        VARCHAR2(11 BYTE )
	,alarm_call2        VARCHAR2(11 BYTE )
);

ALTER TABLE pond_info ADD CONSTRAINT pk_pond_info PRIMARY KEY ( pond_id);
CREATE UNIQUE INDEX udx_pond_name ON pond_info( user_id, pond_name); 

CREATE SEQUENCE pond_id_seq     START WITH 1;

CREATE TABLE device_data_hist (           ---- 这个表名不太好，需重新起名
     device_sn                  NUMBER(9,0)       NOT NULL 
	,device_id                  VARCHAR2(15 BYTE) NOT NULL 
	,pond_id                    NUMBER(9,0)       NOT NULL 
	,dev_incoming_logid         NUMBER(15,0)      NOT NULL 
	,eff_dt                     DATE              NOT NULL  
	,exp_dt                     DATE
	,sync_dt                    DATE 
	,surface_temperature        NUMBER(4,1)
	,deep_temperature           NUMBER(4,1)
	,surface_oxy                NUMBER(4,1)
	,deep_oxy                   NUMBER(4,1)
	,ph_value                   NUMBER(4,1)
	,other_value                NUMBER(4,1)
	,oxy_pole_status            VARCHAR2(1 BYTE)  
	,surface_water_pump_status  VARCHAR2(1 BYTE)  
	,deep_water_pump_status     VARCHAR2(1 BYTE)  
	,ph_sensor_status           VARCHAR2(1 BYTE)     
	,ammonia_nitrogen_status    VARCHAR2(1 BYTE)
	,other_status               VARCHAR2(1 BYTE)
	,temp_status                VARCHAR2(1 BYTE)
	,power_supply_status        VARCHAR2(1 BYTE)  
);
CREATE INDEX idx_device_data_hist ON device_data_hist( device_id, eff_dt);

CREATE TABLE device_data (           ---- 这个表名不太好，需重新起名
     device_sn                  NUMBER(9,0)       NOT NULL 
	,device_id                  VARCHAR2(15 BYTE) NOT NULL 
	,dev_incoming_logid         NUMBER(15,0)      NOT NULL 
	,incoming_dt                DATE             DEFAULT SYSDATE NOT NULL  
	,pond_id                    NUMBER (9,0) 
	,surface_temperature        NUMBER(4,1)
	,deep_temperature           NUMBER(4,1)
	,surface_oxy                NUMBER(4,1)
	,deep_oxy                   NUMBER(4,1)
	,ph_value                   NUMBER(4,1)
	,other_value                NUMBER(4,1)
	,oxy_pole_status            VARCHAR2(1 BYTE)  
	,surface_water_pump_status  VARCHAR2(1 BYTE)  
	,deep_water_pump_status     VARCHAR2(1 BYTE)  
	,ph_sensor_status           VARCHAR2(1 BYTE)     
	,ammonia_nitrogen_status    VARCHAR2(1 BYTE)
	,other_status               VARCHAR2(1 BYTE)
	,temp_status                VARCHAR2(1 BYTE)
	,power_supply_status        VARCHAR2(1 BYTE)  
);

ALTER TABLE device_data ADD CONSTRAINT pk_device_data PRIMARY KEY (device_id) ;

CREATE TABLE device_align_hist(
     device_sn               NUMBER(9,0)         NOT NULL 
	,device_id               VARCHAR2(15 BYTE)   NOT NULL 
	,dev_incoming_logid	     NUMBER(15,0)        NOT NULL
	,eff_dt                  DATE                NOT NULL
	,exp_dt                  DATE
	,temperature             NUMBER(4,1)
	,oxy_voltage_mv          NUMBER(5,0)
	,oxy_align_ratio         NUMBER(6,0)
	,ph_base_voltage_mv      NUMBER(5,0)
	,ph_align_ratio          NUMBER(5,0)
	,ammonia_nitrogen        NUMBER(5,0)
);

CREATE INDEX idx_device_align_hist ON device_align_hist ( device_id, eff_dt);

CREATE TABLE device_align(
     device_sn               NUMBER(9,0)         NOT NULL 
	,device_id               VARCHAR2(15 BYTE)   NOT NULL 
	,dev_incoming_logid	     NUMBER(15,0)        NOT NULL
	,temperature             NUMBER(4,1)
	,oxy_voltage_mv          NUMBER(5,0)
	,oxy_align_ratio         NUMBER(6,0)
	,ph_base_voltage_mv      NUMBER(5,0)
	,ph_align_ratio          NUMBER(5,0)
	,ammonia_nitrogen        NUMBER(5,0)
);

ALTER TABLE device_align ADD CONSTRAINT pk_device_align PRIMARY KEY ( device_id);

CREATE TABLE device_version_hist (
     device_sn               NUMBER(9,0)         NOT NULL 
	,device_id               VARCHAR2(15 BYTE)   NOT NULL 
	,dev_incoming_logid	     NUMBER(15,0)        NOT NULL
	,eff_dt                  DATE                NOT NULL
	,exp_dt                  DATE
	,main_controller_hw      VARCHAR2(13 BYTE)
	,main_controller_sw      VARCHAR2(13 BYTE)
	,float_dev_id            VARCHAR2(15 BYTE)
	,float_dev_hw            VARCHAR2(13 BYTE)
	,float_dev_sw            VARCHAR2(13 BYTE)
 );
CREATE INDEX  idx_device_version_hist ON device_version_hist( device_id, eff_dt);


CREATE TABLE device_version (
     device_sn               NUMBER(9,0)         NOT NULL 
	,device_id               VARCHAR2(15 BYTE)   NOT NULL 
	,dev_incoming_logid	     NUMBER(15,0)        NOT NULL
	,main_controller_hw      VARCHAR2(13 BYTE)
	,main_controller_sw      VARCHAR2(13 BYTE)
	,float_dev_id            VARCHAR2(15 BYTE)
	,float_dev_hw            VARCHAR2(13 BYTE)
	,float_dev_sw            VARCHAR2(13 BYTE)
 );
ALTER TABLE device_version ADD CONSTRAINT pk_device_version PRIMARY KEY ( device_id);
 
CREATE TABLE device_sensor_threshold_hist (
     device_sn               NUMBER(9,0)         NOT NULL 
	,device_id               VARCHAR2(15 BYTE)   NOT NULL 
	,dev_incoming_logid	     NUMBER(15,0)        NOT NULL
	,eff_dt                  DATE                NOT NULL
	,exp_dt                  DATE
	,oxy_min                 NUMBER(5,0)         ---- xx.x mg/L 
	,oxy_max                 NUMBER(5,0)          
	,ph_min                  NUMBER(5,0)         ---- xx.x 
	,ph_max                  NUMBER(5,0)
	,ammonia_nitrogen        NUMBER(5,0)         ---- xxx   
	,temperature_min         NUMBER(5,0)         ---- xx.x C 
);

CREATE INDEX  idx_dev_sensor_threshold_hist ON device_sensor_threshold_hist( device_id, eff_dt);

CREATE TABLE device_sensor_threshold (
     device_sn               NUMBER(9,0)         NOT NULL 
	,device_id               VARCHAR2(15 BYTE)   NOT NULL 
	,dev_incoming_logid	     NUMBER(15,0)        NOT NULL
	,oxy_min                 NUMBER(5,0)         ---- xx.x mg/L 
	,oxy_max                 NUMBER(5,0)          
	,ph_min                  NUMBER(5,0)         ---- xx.x 
	,ph_max                  NUMBER(5,0)
	,ammonia_nitrogen        NUMBER(5,0)         ---- xxx   
	,temperature_min         NUMBER(5,0)         ---- xx.x C 
	,temperature_max         NUMBER(5,0)         ---- xx.x C 
);

ALTER TABLE device_sensor_threshold ADD CONSTRAINT pk_device_sensor_threshold PRIMARY KEY ( device_id);

CREATE TABLE addr_code (
	addr_id         NUMBER(4,0) ,
	name            VARCHAR2 (9 CHAR),
	parent_id        NUMBER(3,0),
	py_initial      VARCHAR2 (7 BYTE),
	py_initials     VARCHAR2 (7  BYTE),
	py_full         VARCHAR2 (19 BYTE),
	extra           VARCHAR2 (12 CHAR),
	suffix          VARCHAR2 (7  CHAR),
	admin_div_code  VARCHAR2 (9  BYTE),
	area_code       VARCHAR2 (6  BYTE),
	seq_no          NUMBER(2,0) 
);

ALTER TABLE addr_code ADD CONSTRAINT  pk_addr_code PRIMARY KEY ( addr_id);
CREATE INDEX idx_addr_code_parent_id ON addr_code (parent_id); 

CREATE TABLE geo_countries
(
   country_id         NUMBER (7, 0),
   locale_code        VARCHAR2 (7 BYTE),
   continent_code     VARCHAR2 (2 BYTE),
   continent_name     VARCHAR2 (4 CHAR),
   country_iso_code   VARCHAR2 (2 BYTE),
   country_name       VARCHAR2 (30 CHAR),
   country_code3      VARCHAR2 (3 BYTE)
);

ALTER TABLE geo_countries ADD CONSTRAINT pk_geo_contries PRIMARY KEY (country_id);
CREATE INDEX idx_geo_countries_cont_id ON geo_countries ( continent_code ); 

create materialized view continent_list 
refresh on commit 
as 
select continent_code, continent_name, count(*) country_ct
 from geo_countries
group by continent_code, continent_name
;


CREATE TABLE debug_log(
		   object_name  VARCHAR2 (30),
		   line         NUMBER   (6,0),
		   topic        VARCHAR2 (36),
		   LOG          VARCHAR2 (1024),
		   elapsed      NUMBER,
		   runningtime  varchar2 (16),
		   logts        TIMESTAMP DEFAULT SYSTIMESTAMP,
		   SESSION_ID   NUMBER,
		   PROGRAM      VARCHAR2 (50),
		   HOST         VARCHAR2 (32),
		   DBUSER       VARCHAR2 (32),
		   OSUSER       VARCHAR2 (32)
		);